package com.baturu.vin.benz;

import com.baturu.carvin.dtos.CarTypeDTO;
import com.baturu.carvin.service.CarTypeService;
import com.baturu.vin.service.VinCarTypeQueryService;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by ab on 2016/10/28.
 */
public class BenzBrand extends BaseTest {

    @Autowired
    private VinCarTypeQueryService vinCarTypeQueryService;
    @Autowired
    private CarTypeService carTypeService;

    @Test
    public void findBrand(){
        String vin = "LB1WA5882A8007497";
        List<CarTypeDTO> btrCarTypeDTOs = carTypeService.findCarTypeByVinCode(vin);
        System.out.println(btrCarTypeDTOs.get(0).getVerifyVinCodeResult());
    }

    @Test
    public void teee() {
        System.out.println(1);
    }


    @Test
    public void testBrand() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        String dburl = "jdbc:mysql://192.168.0.203:3306/benzepc?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true&zeroDateTimeBehavior=convertToNull";
        String uname = "liangguanglong";
        String pw = "lWT0Ri2vZ2Ciwg";
        Connection conn = DriverManager.getConnection(dburl, uname, pw);
        String query = "select DISTINCT chassis,`engine`,transmission,salesName,whc,productTime " +
                "from benz_cartype group by chassis,`engine`,transmission,salesName,whc,productTime";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        if (rs == null) {
            rs.close();
            stmt.close();
            conn.close();
            return;
        }
        String carSystem = null;
        String carType = null;
        String productTime = null;
        String whc = null;
        String engine = null;
        String transmission = null;
        String chassis = null;
        StringBuffer sb = new StringBuffer();
        while (rs.next()) {
            chassis = rs.getString(1);
            engine = rs.getString(2);
            transmission = rs.getString(3);
            carType = rs.getString(4);
            whc = rs.getString(5);
            productTime = rs.getString(6);
            if ("NULL".equalsIgnoreCase(engine)) {
                engine = null;
            }
            if ("NULL".equalsIgnoreCase(transmission)) {
                transmission = null;
            }
            if (Integer.parseInt(productTime) > 40) {
                productTime = "19" + productTime;
            } else {
                productTime = "20" + productTime;
            }
            if (StringUtils.isNotBlank(carType)) {
                String[] cs = carType.split(" ");
                if (cs.length > 1) {
                    if(cs[0].matches("[0-9]+")){
                        try{
                        String[] css = cs[1].split(",");
                       carSystem = css[0];
                        }catch (Exception e){
                            System.out.println(cs[1]);
                        }

                    }else {
                        carSystem = cs[0];
                    }
                }
            }
            sb.append("(" + deal(carSystem) + "," + deal(carType) + "," + deal(productTime) + "," + deal(whc) + "," + deal(engine) + "," + deal(transmission) + "," + deal(chassis) + "),");
            carSystem = null;
            carType = null;
            productTime = null;
            whc = null;
            engine = null;
            transmission = null;
            chassis = null;
        }
        rs.close();

        String insertSql = " insert into benz_salesname (carSystem,carType,productTime,whc,`engine`,transmission,chassis) values ? ";
        String insertStr = sb.toString();
        insertStr = insertStr.substring(0, insertStr.length() - 1);
        ArrayList params = Lists.newArrayList();
        params.add(insertStr);
        insertSql = addParams(insertSql, params);
        stmt.execute(insertSql);
        stmt.close();
        conn.close();
    }


    private String deal(String str){
        if(str == null || str.isEmpty()){
            return null;
        }
        str = regStr(str);
        str="'"+str+"'";
        return str;
    }

    private String regStr(String str){
        String reg="\'";
        return str.replaceAll(reg,"");
    }

    private String addParams(String query, ArrayList params) {
        if (params == null) {
            return query;
        }
        StringBuffer sb = new StringBuffer(query);
        int idx = 0;
        for (int i = 0; i < params.size(); i++) {
            idx = sb.indexOf("?");
            if (idx >= 0) {
                String str = (String) params.get(i);
                if (str == null) {
                    str = "";
                }
                sb.replace(idx, idx + 1, str);
            }
        }
        return sb.toString();
    }

}
